package jp.co.dailyReport.search.sql;

import java.util.Vector;
import java.util.List;
import java.util.ArrayList;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.apache.log4j.Logger;
import jp.co.dailyReport.Executer;
import jp.co.dailyReport.search.SearchDailyReport;
import jp.co.dailyReport.Utilities;

/**
 * 日報検索結果情報を取得するクラスです。
 */
public class SearchDailyReportLoadExecuter
    implements Executer {

	private static Logger log = Logger.getLogger(SearchDailyReportLoadExecuter.class);
	private StringBuffer sqlStatement;
	private List searchObjectList;
	
	private String keyWord;
	private Integer projectId;
	private Integer workItemId;
	private Integer staffId;
	private Integer employmentSystem;
	private String startWorkDate;
	private String endWorkDate;
	private String workDetail;
	private String problem;
	private String toDo;
	private String comment;

	public SearchDailyReportLoadExecuter() {
		searchObjectList = new ArrayList();
		sqlStatement = new StringBuffer();
	}

	public void setKeyWord(String keyWord) {
		this.keyWord = keyWord;
		this.workDetail = keyWord;
		this.problem = keyWord;
		this.toDo = keyWord;
		this.comment = keyWord;

		if(!workDetail.equals("")){
			searchObjectList.add(this.workDetail);
			appendAND("(" + "dailyReport.workDetail like ?");
			searchObjectList.add(this.problem);
			appendOR("dailyReport.problem like ?");
			searchObjectList.add(this.toDo);
			appendOR("dailyReport.toDo like ?");
			searchObjectList.add(this.comment);
			appendOR("dailyReport.comment like ?" + ")");
		}
	}

	public void setProjectId(int projectId) {
		this.projectId = new Integer(projectId);
		if(projectId > 0){
			searchObjectList.add(this.projectId);
			appendAND("project.projectId = ?");
		}
	}

	public void setWorkItemId(int workItemId) {
		this.workItemId = new Integer(workItemId);
		if(workItemId > 0){
			searchObjectList.add(this.workItemId);
			appendAND("dailyReportWorkItem.workItemId = ?");
		}
	}

	public void setStaffId(int staffId) {
		this.staffId = new Integer(staffId);
		if(staffId > 0){
			searchObjectList.add(this.staffId);
			appendAND("staff.staffId = ?");
		}
	}

	public void setEmploymentSystem(int employmentSystem) {
		this.employmentSystem = new Integer(employmentSystem);
		if(employmentSystem >= 0){
			searchObjectList.add(this.employmentSystem);
//			appendAND("staff.employmentSystem = ?");
			appendAND("staff.employmentForm = ?");
		}
	}

	public void setStartWorkDate(String startWorkDate) {
		this.startWorkDate = startWorkDate;
		if(!startWorkDate.equals("00000000")){
			searchObjectList.add(this.startWorkDate);
			appendAND("dailyReport.workDate >= ?");
		}
	}

	public void setEndWorkDate(String endWorkDate) {
		this.endWorkDate = endWorkDate;
		if(!endWorkDate.equals("00000000")){
			searchObjectList.add(this.endWorkDate);
			appendAND("dailyReport.workDate <= ?");
		}
	}

	/**
	 * 設定された条件を基に、日報検索結果情報を取得します。
	 * 
	 * @param conn コネクションオブジェクト
	 * @return 日報検索結果オブジェクトの配列
	 * @throws SQLException
	 */
	public Object[] execute(Connection conn)
		throws SQLException {
		
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		Vector answer = new Vector();
		SearchDailyReport[] searchDailyReports = null;

		String sql = "SELECT DISTINCT dailyReport.dailyReportId dailyReportId,dailyReport.staffId staffId,staff.name name,dailyReport.workDate workDate" +
					" FROM dailyReportWorkItem " +
					" LEFT JOIN dailyReport ON dailyReportWorkItem.dailyReportId = dailyReport.dailyReportId" +
					" LEFT JOIN workItem ON dailyReportWorkItem.workItemId = workItem.workItemId" + 
					" LEFT JOIN project ON workItem.projectId = project.projectId" + 
					" LEFT JOIN staff ON dailyReport.staffId = staff.staffId";

		if(sqlStatement.length() > 0){
			sql = sql + " where " + sqlStatement.toString() + " order by dailyReport.workDate DESC,dailyReport.dailyReportId";
		}
		else{
			sql = sql + " order by dailyReport.workDate DESC,dailyReport.dailyReportId ";
		} 
		log.debug("sql: " + sql);

        try
        {
			pstmt = conn.prepareStatement(sql);

			/* キーワード */
			if(!workDetail.equals("")){
				pstmt.setString(searchObjectList.indexOf(workDetail) + 1, "%" + workDetail + "%");
				releaseSearchObject(workDetail);
			}
			if(!problem.equals("")){
				pstmt.setString(searchObjectList.indexOf(problem) + 1, "%" + problem + "%");
				releaseSearchObject(problem);
			}
			if(!toDo.equals("")){
				pstmt.setString(searchObjectList.indexOf(toDo) + 1, "%" + toDo + "%");
				releaseSearchObject(toDo);
			}
			if(!comment.equals("")){
				pstmt.setString(searchObjectList.indexOf(comment) + 1, "%" + comment + "%");
				releaseSearchObject(comment);
			}
			/* プロジェクト名 */
			if(this.projectId.intValue() > 0){
				pstmt.setInt(searchObjectList.indexOf(projectId) + 1, projectId.intValue());
				releaseSearchObject(projectId);
			}
			/* 作業項目 */
			if(this.workItemId.intValue() > 0){
				pstmt.setInt(searchObjectList.indexOf(workItemId) + 1, workItemId.intValue());
				releaseSearchObject(workItemId);
			}
			/* 氏名 */
			if(staffId.intValue() > 0){
				pstmt.setInt(searchObjectList.indexOf(staffId) + 1, staffId.intValue());
				releaseSearchObject(staffId);
			}
			/* 雇用形態 */
			if(employmentSystem.intValue() >= 0){
				pstmt.setInt(searchObjectList.indexOf(employmentSystem) + 1, employmentSystem.intValue());
				releaseSearchObject(employmentSystem);
			}
			/* 検索期間 */
			if(!startWorkDate.equals("00000000")){
				pstmt.setString(searchObjectList.indexOf(startWorkDate) + 1, startWorkDate);
				releaseSearchObject(startWorkDate);
			}
			if(!endWorkDate.equals("00000000")){
				pstmt.setString(searchObjectList.indexOf(endWorkDate) + 1, endWorkDate);
				releaseSearchObject(endWorkDate);
			}

			SearchDailyReport searchDailyReport;

			rset = pstmt.executeQuery();
				
			while(rset.next()){
				searchDailyReport = new SearchDailyReport();
				searchDailyReport.setDailyReportId(rset.getInt("dailyReportId"));
				searchDailyReport.setStaffId(rset.getInt("staffId"));
				searchDailyReport.setName(Utilities.convUniqueCharacter(rset.getString("name")));
				searchDailyReport.setWorkDate(rset.getDate("workDate"));
				answer.add(searchDailyReport);
			}

			/* 登録はされているが、作業内容は登録されていない日報の検索 */
			/* プロジェクト名、作業項目が条件指定されていない場合に処理する。*/
			if(projectId.intValue() <= 0 
				&& workItemId.intValue() <= 0){

				pstmt.clearParameters();

				sql = "SELECT DISTINCT dailyReport.dailyReportId dailyReportId,dailyReport.staffId staffId,staff.name name,dailyReport.workDate workDate" +
						" FROM dailyReport" +
						" LEFT JOIN staff ON dailyReport.staffId = staff.staffId" +
						" LEFT JOIN dailyReportWorkItem ON dailyReport.dailyReportId = dailyReportWorkItem.dailyReportId" +
						" WHERE dailyReport.totalWorkingHours = 0" +
						" AND dailyReportWorkItem.workItemId IS NULL";
				if(staffId.intValue() > 0 ){
						sql = sql + " AND dailyReport.staffId = " + staffId.intValue();
				}
				if(employmentSystem.intValue() >= 0){
//						sql = sql + " AND staff.employmentSystem = " + employmentSystem.intValue();
						sql = sql + " AND staff.employmentForm = " + employmentSystem.intValue();
				}
				/* 検索期間 */
				if(!startWorkDate.equals("00000000")){
					sql = sql + " AND workDate >= " + startWorkDate;
				}
				if(!endWorkDate.equals("00000000")){
					sql = sql + " AND workDate <= " + endWorkDate;
				}
				/* キーワード */
				if(!keyWord.equals("")){
					sql = sql + " AND (dailyReport.workDetail like " + "'%" + workDetail + "%'";
					sql = sql + " OR dailyReport.problem like " + "'%" + problem + "%'";
					sql = sql + " OR dailyReport.toDo like " + "'%" + toDo + "%'";
					sql = sql + " OR dailyReport.comment like " + "'%" + comment + "%'" + ")";
				}

				sql = sql + " order by dailyReport.workDate DESC,dailyReport.dailyReportId";
				log.debug("sql: " + sql);

				pstmt = conn.prepareStatement(sql);
				rset = pstmt.executeQuery();
				
				while(rset.next()){
					searchDailyReport = new SearchDailyReport();
					searchDailyReport.setDailyReportId(rset.getInt("dailyReportId"));
					searchDailyReport.setStaffId(rset.getInt("staffId"));
					searchDailyReport.setName(Utilities.convUniqueCharacter(rset.getString("name")));
					searchDailyReport.setWorkDate(rset.getDate("workDate"));
					answer.add(searchDailyReport);
				}
			}
		}
        finally
        {
			if(rset != null)
				rset.close();
			if(pstmt != null)
				pstmt.close();
        }
        
        return (SearchDailyReport[])answer.toArray(new SearchDailyReport[0]);
    }

	private void appendAND(String str){
		if(sqlStatement.length() > 0)
			sqlStatement.append(" AND " + str);
		else
			sqlStatement.append(str);
	}

	private void appendOR(String str){
		if(sqlStatement.length() > 0)
			sqlStatement.append(" OR " + str);
		else
			sqlStatement.append(str);
	}

	private void releaseSearchObject(Object obj){
		searchObjectList.set(searchObjectList.indexOf(obj), null);
	}
}
